<?php

function get_student_records($studentid) {
	// This function is actually called by the front-end.
	// It needs to connect to the back-end, get the data,
	// convert it to Exam View's internal data format, and
	// return them to the front-end. With simple data
	// stores, this function may just be a wrapper for
	// fetch_data. But it is sometimes impossible to translate
	// one record at a time, so some dataset-wide calls may
	// have to be made here.
	
	// Fetch the data
	$recordset = fetch_data($studentid);	
	
	// Perform any necessary translations of the data.
	
	return $recordset;
}

function fetch_data ($studentid) {
	
	// Settings you may want to configure for your
	// own database server.
	
	$oracle_server 			= "server/instance";
	$oracle_username 		= "user";
	$oracle_password		= "password";
	
	// This is where your prepared statements will go.
	// You want to pull your required fields out in your
	// select statement because we're using prepared
	// statements and bind_result. Put a question mark
	// where you want the student ID to be placed.
	
	$oracle_query			= 'select t2.s_studentreference "Student reference",t3.e_reference "CP Reference",t4.m_name "CP Title",t5.e_reference "SUB Reference",t6.m_name "SUB Title",t7.vc_name "Pass",count(t8.e_id) "Present Enrolments",t9.s_reference "Year",t9.s_name "FullYear",decode(t6.m_type,\'SQA\',t10.mf_fes2sums,null) "SQA Credit Value",max(t11.sat_start) "Amended" from capd_student t2,capd_modulefessqa t10,capd_person t1,caps_valid_codes t7,caps_system_audit_trail t11,caps_system_audit_details t13,capd_module t6,capd_moduleenrolment t3,capd_moduleenrolment t5,capd_module t12,capd_module t4,capd_moduleenrolment t8,capd_session t9 where t2.s_id(+)=t1.p_id and t7.vc_code(+)=t5.e_pass and t7.vc_domain(+)=\'pass\' and t10.mf_id(+)=t6.m_id and (t3.e_module=t4.m_id) and (t3.e_student=t1.p_id) and (t5.e_student=t1.p_id) and (t5.e_parent=t3.e_id) and (t3.e_status=\'L\') and ((t5.e_pass is not null) or (t6.m_type=\'SQA\')) and (t8.e_student=t1.p_id) and (t8.e_type=\'CP\') and (t8.e_status=\'L\') and (t8.e_module=t12.m_id) and (sysdate between t12.m_start and t12.m_end) and (t4.m_type=\'CP\') and (t5.e_module=t6.m_id) and (t4.m_end between t9.s_start and t9.s_end) and (t2.s_studentreference like :studentid) and (t11.sat_id(+)=t5.e_id) and (t13.sad_id(+)=t11.sat_audit) and (t13.sad_property(+)=\'pass\') group by t2.s_studentreference,t3.e_reference,t4.m_name,t5.e_reference,t6.m_name,t7.vc_name,t9.s_reference,t9.s_name,decode(t6.m_type,\'SQA\',t10.mf_fes2sums,null),t9.s_name,decode(t5.e_pass,\'M\',1,\'P\',2,null,9,3),t2.s_studentreference order by t9.s_name desc,decode(t5.e_pass,\'M\',1,\'P\',2,null,9,3),t2.s_studentreference';
	$oracle_query2 = "SELECT COUNT(*) from capd_module";
	
	$conn = oci_connect($oracle_username, $oracle_password, $oracle_server);
	$query = oci_parse($conn, $oracle_query);
	
	// Now we put the student ID in the right place.
	
	oci_bind_by_name($query, ':studentid', $studentid);
	oci_execute($query);

	$results = array();

	// Now we're reading the returned records, converting
	// them (using process_record) to Exam View's internal
	// data format, and storing the in an array of resuts.
	// If it's not possible to isolate a single record for
	// translation, you should simply return the untranslated
	// recordset here, and call process_entire_recordset()
	// from process_records.

	while ($row = oci_fetch_array($query, OCI_ASSOC)) {
		$results[] = process_record($row);
	}

	return($results);

}

function process_record($record) {

	// Takes in one record as $record and converts
	// it to a record conforming with Exam View's internal
	// data structure. For some data stores, it may not be
	// possible to isolate a single record for conversion.
	// If this is that case, you should write a function
	// called process_entire_recordset() and call it from
	// get_student_records().
	
	$return = new resultRecord();
	
	// Convert the record
	
	$return->courseTitle = $record["CP Title"];
	$return->courseCode = $record["CP Reference"];
	$return->moduleTitle = $record["SUB Title"];
	$return->moduleCode = $record["SUB Reference"];
	$return->period = substr($record["CP Reference"], strpos($record["CP Reference"], "-") + 2, 2);

	if ( isset($record["Pass"]) ) {
		$return->result = $record["Pass"];
	}
	
	// Get the feedback, if any
	if ( isset($record["feedback"]) )
		$return->feedback = $record["feedback"];
	
	
	return ($return);
}

?>